package com.surprise.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.surprise.db.po.City;
import com.surprise.db.vo.IdNameVO;

import lombok.AllArgsConstructor;

/**
 * CityDAO.java
 * @author nick
 * @since 2015年5月9日
 */
@Repository
public class CityDAO extends SimpleDAO<City> {
	public final String columns = "(parentid,name,fullname,pinyin,type,addtime)";
	public final String insertStatement = "(?,?,?,?,?,now())";
	
	@AllArgsConstructor
	public static class CitySetter implements BatchPreparedStatementSetter {
		private List<City> items;
		
		@Override
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			City item = items.get(i);
			int index = 1;
			ps.setLong(index++, item.getParentid());
			ps.setString(index++, item.getName());
			ps.setString(index++, item.getFullname());
			ps.setString(index++, item.getPinyin());
			ps.setInt(index++, item.getType());
		}

		@Override
		public int getBatchSize() {
			return items.size();
		}
	}
	
	public void add(List<City> items) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ")
			.append(getTableName())
			.append(columns)
			.append(" values ")
			.append(insertStatement);
		String sql = sb.toString();
		jdbcTemplate.batchUpdate(sql, new CitySetter(items));
	}
	
	public void edit(City item) {
		StringBuilder sb = new StringBuilder();
		sb.append("update ")
			.append(getTableName())
			.append(" set parentid = ?, name = ?, fullname = ?, pinyin = ?, type = ? where id = ? ");
		String sql = sb.toString();
		jdbcTemplate.update(sql, item.getParentid(),item.getName(),item.getFullname(),item.getPinyin(),item.getType(),item.getId());
	}
	
	/**
	 * 根据城市id获取区域id
	 * @param cityId
	 * @return
	 */
	public List<IdNameVO> findDistrictidByCityId(Long cityId){
		String sql ="select id,name from city where 1=1 and parentid ="+cityId+" and type =3";
		return jdbcTemplate.query(sql, new Object[] {  }, new BeanPropertyRowMapper<IdNameVO>(IdNameVO.class));
	}
	
	public List<City> findDistricts() {
	    String sql = "select * from city where type = 3";
	    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<City>(City.class));
	}
	
	public List<City> findCities() {
	    String sql = "select * from city where type = 2";
	    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<City>(City.class));
	}
}
